package gen.tools.reports;

import gen.tools.Widgets;
import gen.tools.structures.DepMemphis;
import gen.tools.structures.DepMemphisDetail;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;

public class GenQueryMemphisReport extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public GenQueryMemphisReport() {
		super();
	}

	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	@SuppressWarnings("deprecation")
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		final Widgets wid = new Widgets();
		request.setCharacterEncoding(wid.UIcodeSet);
		response.setCharacterEncoding(wid.UIcodeSet);
		response.setContentType("application/octet-stream; charset=iso-8859-1");
		response.setHeader("Content-disposition",
				"attachment; filename=MemphisReport.xls");

		String tmpFileName = System.currentTimeMillis() + ".xls";
		String WebDirPath = request.getSession().getServletContext()
				.getRealPath("/");

		/*final String ENG_FONT_STYLE = "verdana";
		final String CHI_FONT_STYLE = "微軟正黑體";*/
		final String MIX_FONT_STYLE = "Verdana";
		final int BASE_WIDTH = 900;

		int cur = 6;	//目前使用到的row
		
		FileInputStream fis = null;
		FileOutputStream fos = null;
		OutputStream out = null;
		
		final HSSFWorkbook wb = new HSSFWorkbook();

		// 設定字型1 英文字，粗體，白色
		final HSSFFont font1 = wb.createFont();
		font1.setFontName(MIX_FONT_STYLE);
		font1.setColor(IndexedColors.WHITE.getIndex());
		font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font1.setFontHeightInPoints((short) 14);
		
		final HSSFFont font2 = wb.createFont();
		font2.setFontName(MIX_FONT_STYLE);
		font2.setColor(IndexedColors.BLACK.getIndex());
		font2.setFontHeightInPoints((short) 12);

		final HSSFFont font3 = wb.createFont();
		font3.setFontName(MIX_FONT_STYLE);
		font3.setColor(IndexedColors.BLACK.getIndex());
		font3.setFontHeightInPoints((short) 10);
		
		final CellStyle csvs = wb.createCellStyle();
		csvs.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		csvs.setFont(font3);
		// 設定cell style的變數 cs1， 置中、字型1
		final CellStyle cs1 = wb.createCellStyle();
		cs1.setAlignment( HSSFCellStyle.ALIGN_CENTER );
		cs1.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		cs1.setFont(font1);
		cs1.setWrapText(true);
		cs1.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
		cs1.setFillPattern(CellStyle.SOLID_FOREGROUND);
		
		
		final CellStyle cs2 = wb.createCellStyle();
		cs2.setAlignment( HSSFCellStyle.ALIGN_RIGHT );
		cs2.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		cs2.setFont(font3);
		
		final CellStyle cs3 = wb.createCellStyle();
		cs3.setAlignment( HSSFCellStyle.ALIGN_LEFT );
		cs3.setWrapText(true);//自動換行
		cs3.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		cs3.setFont(font3);
		
		final CellStyle cs4 = wb.createCellStyle();
		cs4.setAlignment( HSSFCellStyle.ALIGN_CENTER );
		cs4.setVerticalAlignment( CellStyle.VERTICAL_CENTER );
		cs4.setFont(font3);
		
		final HSSFSheet fs = wb.createSheet("查詢結果");
		
		fs.createRow(0).createCell(0).setCellValue(new HSSFRichTextString("製表日期："
				+ wid.changeTimeFormat(new Date())));

		final HSSFRow r5 = fs.createRow(5);

		final HSSFCell cA5 = r5.createCell(0);
		cA5.setCellValue("#");
		cA5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cA5.getColumnIndex(), BASE_WIDTH * 2 );

		final HSSFCell cB5 = r5.createCell(1);
		cB5.setCellValue("部署記錄ID");
		cB5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cB5.getColumnIndex());
		
		final HSSFCell cC5 = r5.createCell(2);
		cC5.setCellValue("部署人員");
		cC5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cC5.getColumnIndex());		

		final HSSFCell cD5 = r5.createCell(3);
		cD5.setCellValue("部署日期");
		cD5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cD5.getColumnIndex());		

		final HSSFCell cE5 = r5.createCell(4);
		cE5.setCellValue("SVN資訊");
		cE5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.setColumnWidth(cE5.getColumnIndex(), BASE_WIDTH * 16 );
		
		final HSSFCell cF5 = r5.createCell(5);
		cF5.setCellValue("部署細項ID");
		cF5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cF5.getColumnIndex());		
		
		final HSSFCell cG5 = r5.createCell(6);
		cG5.setCellValue("SQUAD");
		cG5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cG5.getColumnIndex());		
		
		final HSSFCell cH5 = r5.createCell(7);
		cH5.setCellValue("部署/下線");
		cH5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cH5.getColumnIndex());		
		
		final HSSFCell cI5 = r5.createCell(8);
		cI5.setCellValue("檔案名稱");
		cI5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cI5.getColumnIndex());		
		
		final HSSFCell cJ5 = r5.createCell(9);
		cJ5.setCellValue("相對路徑");
		cJ5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cJ5.getColumnIndex());		
		
		final HSSFCell cK5 = r5.createCell(10);
		cK5.setCellValue("版次");
		cK5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cK5.getColumnIndex());		
		
		final HSSFCell cL5 = r5.createCell(11);
		cL5.setCellValue("估計值");
		cL5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cL5.getColumnIndex());		
		
		final HSSFCell cM5 = r5.createCell(12);
		cM5.setCellValue("是否成功");
		cM5.setCellStyle(cs1); // 讓此儲存格擁有此種style
		fs.autoSizeColumn(cM5.getColumnIndex());		
		
		fs.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

		/******	以上是Table的表頭，以下是表身	******/
		
		final String strSQL="SELECT t1.depMemphisSN,t1.depType,t1.svnUSR,t1.svnURL,t1.cDT,t2.depMemphisDetailId,t2.isDep,t2.squadId,t2.fileName,t2.fileVer,t2.relativePath,t2.isSuccess,t2.errMsg,t2.isAssume,t3.empName FROM depmemphis t1,depmemphisdetail t2,emps t3 WHERE t1.depMemphisSN=t2.depMemphisSN AND t1.creatorId=t3.empId ORDER BY t1.depMemphisSN DESC,t2.depMemphisDetailId ASC";
		
		final ArrayList<DepMemphis> dms = new ArrayList<DepMemphis>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int preSN = -1;
		try {
			conn = wid.getConn();
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			
			while ( rs.next() ) {
				DepMemphisDetail dmd = new DepMemphisDetail();
				dmd.setDepMemphisDetailId(rs.getInt("depMemphisDetailId"));	
				dmd.setSquadId(rs.getString("squadId"));
				dmd.setFileName(rs.getString("fileName"));
				dmd.setFileVer(rs.getInt("fileVer"));
				dmd.setRelativePath(rs.getString("relativePath"));
				dmd.setSuccess("t".equalsIgnoreCase( rs.getString("isSuccess") ));
				dmd.setErrMsg( rs.getString("errMsg"));
				dmd.setAssume( "t".equalsIgnoreCase(rs.getString("isAssume")));	//是否為理論值
				dmd.setDep( "t".equalsIgnoreCase(rs.getString("isDep")));			//是否為部署
				
				if( rs.getInt("depMemphisSN") == preSN ) {
					dms.get(dms.size()-1).getDmds().add(dmd);
				} else {	//表示是新的一筆記錄
					preSN = rs.getInt("depMemphisSN");
					DepMemphis dm = new DepMemphis();
					dm.setDepMemphisSN(rs.getInt("depMemphisSN"));
					dm.setSvnURL(rs.getString("svnURL"));
					dm.setSvnUSR(rs.getString("svnUSR"));
					dm.setcDT( changeTimeFormatUI4File(rs.getTimestamp("cDT")));
					dm.setCreator(rs.getString("empName"));
					dm.setDepType( rs.getInt("depType") );	//部署類型 1.手動部署 2.重新部署	
					ArrayList<DepMemphisDetail> dmds = new ArrayList<DepMemphisDetail>();
					dmds.add(dmd);
					dm.setDmds(dmds);
					dms.add(dm);
				}
			}
			
			
			for(int i=0; i < dms.size(); i++){
				int ds = dms.get(i).getDmds().size();
				fs.createRow(cur).createCell(0).setCellValue( (i+1) );													//流水號
				fs.getRow(cur).createCell(1).setCellValue( dms.get(i).getDepMemphisSN() );							//部署記錄ID
				fs.getRow(cur).createCell(2).setCellValue( dms.get(i).getCreator() );								//部署人員
				fs.getRow(cur).createCell(3).setCellValue( dms.get(i).getcDT() );									//部署日期
				fs.getRow(cur).createCell(4).setCellValue( dms.get(i).getSvnURL()+"\n"+dms.get(i).getSvnUSR() );		//SVN資訊
				
				fs.getRow(cur).getCell(0).setCellStyle(cs2);
				fs.getRow(cur).getCell(1).setCellStyle(cs2);
				fs.getRow(cur).getCell(2).setCellStyle(cs3);
				fs.getRow(cur).getCell(3).setCellStyle(cs3);
				fs.autoSizeColumn(3);
				fs.getRow(cur).getCell(4).setCellStyle(cs3);
				
				for(int j=0; j < dms.get(i).getDmds().size(); j++){
					String depType = "";
					if( dms.get(i).getDepType()==2 ) {
						if ( dms.get(i).getDmds().get(j).isDep() ) {
							depType = "重新部署";
						} else {
							depType = "暫時下線";
						}
					} else {
						if ( dms.get(i).getDmds().get(j).isDep() ) {
							depType = "部署";
						} else {
							depType = "下線";
						}
					}
					
					if( j==0) {
						fs.getRow(cur).createCell(5).setCellValue( dms.get(i).getDmds().get(j).getDepMemphisDetailId() );		//部署細項ID
						fs.getRow(cur).createCell(6).setCellValue( dms.get(i).getDmds().get(j).getSquadId() );					//SQUAD
						fs.getRow(cur).createCell(7).setCellValue( depType );	//部署/下線
						fs.getRow(cur).createCell(8).setCellValue( dms.get(i).getDmds().get(j).getFileName() );					//檔案名稱
						fs.getRow(cur).createCell(9).setCellValue( dms.get(i).getDmds().get(j).getRelativePath() );				//相對路徑
						fs.getRow(cur).createCell(10).setCellValue( dms.get(i).getDmds().get(j).getFileVer() );					//版次
						fs.getRow(cur).createCell(11).setCellValue( dms.get(i).getDmds().get(j).isAssume()?"是":"否" );			//是否為理論值
						fs.getRow(cur).createCell(12).setCellValue( dms.get(i).getDmds().get(j).isSuccess()?"是":"否" );			//是否成功

						fs.getRow(cur).getCell(5).setCellStyle(cs2);
						fs.getRow(cur).getCell(6).setCellStyle(cs3);
						fs.getRow(cur).getCell(7).setCellStyle(cs4);
						fs.getRow(cur).getCell(8).setCellStyle(cs3);
						fs.getRow(cur).getCell(9).setCellStyle(cs3);
						fs.getRow(cur).getCell(10).setCellStyle(cs2);
						fs.getRow(cur).getCell(11).setCellStyle(cs4);
						fs.getRow(cur).getCell(12).setCellStyle(cs4);
					} else {
						fs.createRow(cur+j).createCell(5).setCellValue( dms.get(i).getDmds().get(j).getDepMemphisDetailId() );		//部署細項ID
						fs.getRow(cur+j).createCell(6).setCellValue( dms.get(i).getDmds().get(j).getSquadId() );					//SQUAD
						fs.getRow(cur+j).createCell(7).setCellValue( depType );	//部署/下線
						fs.getRow(cur+j).createCell(8).setCellValue( dms.get(i).getDmds().get(j).getFileName() );					//檔案名稱
						fs.getRow(cur+j).createCell(9).setCellValue( dms.get(i).getDmds().get(j).getRelativePath() );				//相對路徑
						fs.getRow(cur+j).createCell(10).setCellValue( dms.get(i).getDmds().get(j).getFileVer() );					//版次
						fs.getRow(cur+j).createCell(11).setCellValue( dms.get(i).getDmds().get(j).isAssume()?"是":"否" );			//是否為理論值
						fs.getRow(cur+j).createCell(12).setCellValue( dms.get(i).getDmds().get(j).isSuccess()?"是":"否" );			//是否成功
						
						fs.getRow(cur+j).setRowStyle(csvs);
						fs.getRow(cur+j).getCell(5).setCellStyle(cs2);
						fs.getRow(cur+j).getCell(6).setCellStyle(cs3);
						fs.getRow(cur+j).getCell(7).setCellStyle(cs4);
						fs.getRow(cur+j).getCell(8).setCellStyle(cs3);
						fs.getRow(cur+j).getCell(9).setCellStyle(cs3);
						fs.getRow(cur+j).getCell(10).setCellStyle(cs2);
						fs.getRow(cur+j).getCell(11).setCellStyle(cs4);
						fs.getRow(cur+j).getCell(12).setCellStyle(cs4);
					}
				}
				fs.autoSizeColumn(6);
				fs.autoSizeColumn(8);
				fs.autoSizeColumn(9);
				
				//合併儲存格
				fs.addMergedRegion(new CellRangeAddress(cur, (cur+ds-1), 0, 0));
				fs.addMergedRegion(new CellRangeAddress(cur, (cur+ds-1), 1, 1));
				fs.addMergedRegion(new CellRangeAddress(cur, (cur+ds-1), 2, 2));
				fs.addMergedRegion(new CellRangeAddress(cur, (cur+ds-1), 3, 3));
				fs.addMergedRegion(new CellRangeAddress(cur,(cur+ds-1), 4, 4));
				
				fs.getRow(cur).setRowStyle(csvs);
				
				cur += ds;
			}
			
			
			/* 以上Excel內容製作完成，以下開始產生檔案 */
			
			fos = new FileOutputStream(new File(WebDirPath + tmpFileName));
			wb.write(fos);
			fos.close();
			out = response.getOutputStream();
			fis = new FileInputStream(WebDirPath + tmpFileName);
			byte[] buffer = new byte[4096];
			int length;
			while ((length = fis.read(buffer)) > 0) {
				out.write(buffer, 0, length);
			}
			fis.close();
			out.flush();
			
		} catch (Exception e) {
			wid.errLog(e);
		} finally {
			try {
				if ( fis !=null ) {fis.close();}
				if ( out !=null ) {out.close();}
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}
			} catch (SQLException e) {e.printStackTrace();}
		}
	}

	private String changeTimeFormatUI4File(Date dt) {
		return new java.text.SimpleDateFormat("yyyy-MM-dd").format(dt)+" "+new java.text.SimpleDateFormat("HH:mm:ss").format(dt);
	}
}
